USE arere
GO

/* 
	Kill all of the tables
	if they already exist. 
*/
DROP TABLE ChildAreas
GO
DROP TABLE AdjacentAreas
GO
DROP TABLE Players
GO
DROP TABLE Areas
GO
DROP TABLE Proficiencies
GO
DROP TABLE PlayerProficiencies
GO

/*
	Kill all of the stored procedures
	in case they already exist
*/
DROP PROCEDURE AreaGetById
GO
DROP PROCEDURE AreaGetChildAreas
GO
DROP PROCEDURE AreaGetAdjacentAreas
GO
DROP PROCEDURE AreaChangeName
GO
DROP PROCEDURE AreaCreateArea
GO
DROP PROCEDURE AreaAddChildArea
GO
DROP PROCEDURE AreaRemoveChildArea
GO
DROP PROCEDURE AreaAddAdjacentArea
GO
DROP PROCEDURE AreaRemoveAdjacentArea
GO
DROP PROCEDURE AreaDelete
GO

/*
	Create the tables
*/
CREATE TABLE Areas (
	AreaId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	Name NVARCHAR(50) NOT NULL
)
GO

CREATE TABLE Players (
	PlayerId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	UserId UNIQUEIDENTIFIER NOT NULL,
	Name NVARCHAR(50) NOT NULL,
	CurrentArea UNIQUEIDENTIFIER NULL,
	CONSTRAINT fkPlayersCurrentAreaId 
		FOREIGN KEY(CurrentArea) REFERENCES Areas(AreaId)
)
GO

CREATE TABLE AdjacentAreas (
	PrimaryAreaId UNIQUEIDENTIFIER NOT NULL,
	AdjacentAreaId UNIQUEIDENTIFIER NOT NULL,
	PRIMARY KEY(PrimaryAreaId, AdjacentAreaId),
	CONSTRAINT fkAdjacentAreasPrimaryAreaId 
		FOREIGN KEY(PrimaryAreaId) REFERENCES Areas(AreaId),
	CONSTRAINT fkAdjacentAreasAdjacentAreaId 
		FOREIGN KEY(AdjacentAreaId) REFERENCES Areas(AreaId)
)
GO

CREATE TABLE ChildAreas (
	ParentAreaId UNIQUEIDENTIFIER NOT NULL,
	ChildAreaId UNIQUEIDENTIFIER NOT NULL,
	PRIMARY KEY(ParentAreaId, ChildAreaId),
	CONSTRAINT ChildAreasParentAreaId
		FOREIGN KEY(ParentAreaId) REFERENCES Areas(AreaId),
	CONSTRAINT ChildAreasChildAreaId
		FOREIGN KEY(ChildAreaId) REFERENCES Areas(AreaId)
)
GO

/*

Proficencies:
	Each proficiency has an increase rate,
	baselevel, and level growth rate.
	When the player does an action that deals
	with the proficiency, they get increaseRate
	experience points. It takes (baselevel
	* levelgrowthrate * player's level) experience points
	to increment the player's modifier.

*/
CREATE TABLE Proficiencies (
	ProficiencyId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	Name NVARCHAR(50) NOT NULL,
	Description TEXT NOT NULL,
	IncreaseRate INT NOT NULL,
	BaseLevel INT NOT NULL,
	LevelGrowthRate DECIMAL NOT NULL DEFAULT(1.0)
)
GO

/*
CREATE TABLE Item (
	ItemId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	Name NVARCHAR(50) NOT NULL,
	Description TEXT NOT NULL,
	ProficiencyId UNIQUEIDENTIFIER NOT NULL
)
GO
*/

CREATE TABLE PlayerProficiencies (
	PlayerId UNIQUEIDENTIFIER NOT NULL,
	ProficiencyId UNIQUEIDENTIFIER NOT NULL,
	Modifier INT NOT NULL,
	Experience INT NOT NULL DEFAULT(0),
	Level INT NOT NULL DEFAULT(0),
	PRIMARY KEY(PlayerId, ProficiencyId),
	CONSTRAINT PlayerProficienciesPlayerId
		FOREIGN KEY(PlayerId) REFERENCES Player(PlayerId),
	CONSTRAINT PlayerProficienciesProficiencyId
		FOREIGN KEY(ProficiencyId) REFERENCES Proficiencies(ProficiencyId)
)
GO

/* ATTACHING TO SQLEXPRESS TYPE FORMS AUTHENTICATION DB */
ALTER TABLE Players
	ADD CONSTRAINT fkPlayersUserId
		FOREIGN KEY(PlayerId) REFERENCES aspnet_Users(UserId)
GO

/*
	Create the area stored procedures
*/
CREATE PROCEDURE AreaGetById
@AreaId UNIQUEIDENTIFIER
AS
BEGIN
	SELECT * FROM Areas WHERE AreaId=@AreaId
END
GO

CREATE PROCEDURE AreaGetChildAreas
@AreaId UNIQUEIDENTIFIER
AS
BEGIN
	SELECT Areas.* FROM Areas WHERE AreaId IN
		(SELECT ChildAreas.ChildAreaId FROM Areas
		INNER JOIN ChildAreas ON Areas.AreaId = ChildAreas.ParentAreaId
		WHERE (ChildAreas.ParentAreaId=@AreaId))
END
GO

CREATE PROCEDURE AreaGetAdjacentAreas
@AreaId UNIQUEIDENTIFIER
AS
BEGIN
	SELECT Areas.* FROM Areas WHERE AreaId IN
		(SELECT AdjacentAreas.AdjacentAreaId FROM Areas
		INNER JOIN AdjacentAreas ON Areas.AreaId = AdjacentAreas.PrimaryAreaId
		WHERE(AdjacentAreas.PrimaryAreaId=@AreaId))
END
GO

CREATE PROCEDURE AreaChangeName
@AreaId UNIQUEIDENTIFIER, @NewName NVARCHAR(50)
AS
BEGIN
	UPDATE Areas SET Name=@NewName WHERE AreaId=@AreaId
END
GO

CREATE PROCEDURE AreaCreateArea
@AreaId UNIQUEIDENTIFIER, @Name NVARCHAR(50)
AS
BEGIN
	INSERT INTO Areas(AreaId, Name) VALUES (@AreaId, @Name)
END
GO

CREATE PROCEDURE AreaAddChildArea
@ParentAreaId UNIQUEIDENTIFIER, @ChildAreaId UNIQUEIDENTIFIER
AS
BEGIN
	INSERT INTO ChildAreas(ParentAreaId, ChildAreaId) 
	VALUES(@ParentAreaId, @ChildAreaId)
END
GO

CREATE PROCEDURE AreaRemoveChildArea
	@ParentAreaId UNIQUEIDENTIFIER, @ChildAreaId UNIQUEIDENTIFIER
AS
BEGIN
	DELETE FROM ChildAreas 
	WHERE ParentAreaId=@ParentAreaId 
	AND ChildAreaId=@ChildAreaId
END
GO

CREATE PROCEDURE AreaAddAdjacentArea
	@PrimaryAreaId UNIQUEIDENTIFIER, @AdjacentAreaId UNIQUEIDENTIFIER
AS
BEGIN
	INSERT INTO AdjacentAreas(PrimaryAreaId, AdjacentAreaId) 
	VALUES (@PrimaryAreaId, @AdjacentAreaId)
END
GO

CREATE PROCEDURE AreaRemoveAdjacentArea
	@PrimaryAreaId UNIQUEIDENTIFIER, @AdjacentAreaId UNIQUEIDENTIFIER
AS
BEGIN
	DELETE FROM AdjacentAreas 
	WHERE PrimaryAreaId=@PrimaryAreaId 
	AND AdjacentAreaId=@AdjacentAreaId
END
GO

CREATE PROCEDURE AreaDelete
@Id UNIQUEIDENTIFIER
AS
BEGIN
	DELETE FROM ChildAreas WHERE ParentAreaId=@Id
	DELETE FROM ChildAreas WHERE ChildAreaId=@Id
	DELETE FROM AdjacentAreas WHERE PrimaryAreaId=@Id
	DELETE FROM AdjacentAreas WHERE AdjacentAreaId=@Id
	DELETE FROM Areas WHERE AreaId=@Id
END
GO